package messageControl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import messageControl.Message;
import util.mysqlUtil;



public class MessageDao {
	String tableName;

	public MessageDao(String tableName) {
		this.tableName = tableName;
	}

	public Message select(Connection conn, int messageId) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from "
					+ tableName + " where number = ?");
			pstmt.setInt(1, messageId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return makeMessageFromResultSet(rs);
			} else {
				return null;
			}
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}
	}

	protected Message makeMessageFromResultSet(ResultSet rs)
			throws SQLException {
		// number id title start finish time content date
		Message message = new Message();
		message.setNumber(rs.getInt("number"));
		message.setId(rs.getString("id"));
		message.setTitle(rs.getString("title"));
		message.setStart(rs.getString("start"));
		message.setFinish(rs.getString("finish"));
		message.setTime(rs.getString("time"));
		message.setContent(rs.getString("content"));
		message.setDate(rs.getString("date"));

		return message;
	}

	public int selectCount(Connection conn) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			//rs = stmt.executeQuery("select count(*) from 'carpool'.'"
				//	+ tableName + "'");
			rs = stmt.executeQuery("select count(*) from "+tableName);
			rs.next();
			return rs.getInt(1);
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(stmt);
		}
	}

	public int delete(Connection conn, int messageId) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("delete from 'carpool'.'" + tableName
					+ "' where number = ?");
			pstmt.setInt(1, messageId);
			return pstmt.executeUpdate();
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}
	}

	public int insert(Connection conn, Message message) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn
					.prepareStatement("INSERT INTO "
							+ tableName
							+ " (title ,id ,start ,finish, time, content, date) "
							+ "VALUES (?,?,?,?,?,?,?);");
			pstmt.setString(1, message.getTitle());
			pstmt.setString(2, message.getId());
			pstmt.setString(3, message.getStart());
			pstmt.setString(4, message.getFinish());
			pstmt.setString(5, message.getTime());
			pstmt.setString(6, message.getContent());
			pstmt.setString(7, message.getDate());
			return pstmt.executeUpdate();
		} finally {
			mysqlUtil.close(pstmt);
		}
	}
	
	public List<Message> search(Connection conn, String start, String end) throws SQLException{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn
					.prepareStatement("select * from(select * from(select * from "+tableName+" m order by m.number desc)a where start =?)b where finish =?;");
			start.trim();
			end.trim();
			pstmt.setString(1, start);
			pstmt.setString(2, end);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				List<Message> messageList = new ArrayList<Message>();
				do {
					messageList.add(makeMessageFromResultSet(rs));
				} while (rs.next());
				return messageList;
			} else {
				return Collections.emptyList();
			}
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}	
	}
	
	public List<Message> selectList(Connection conn, int firstRow, int endRow)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn
					.prepareStatement("select * from(select * from(select * from "+tableName+" m order by m.number desc)a where number <=?)b where number >=?;");

			pstmt.setInt(1, endRow);
			pstmt.setInt(2, firstRow);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				List<Message> messageList = new ArrayList<Message>();
				do {
					messageList.add(makeMessageFromResultSet(rs));
				} while (rs.next());
				return messageList;
			} else {
				return Collections.emptyList();
			}
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}
	}
}